﻿
/****** Object:  StoredProcedure [dbo].[sp_JobSchedule_SystemSettings_Select]    Script Date: 01/12/2025 12:40:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sp_JobSchedule_SystemSettings_Select]
AS
-- =============================================
-- Author:		Andrew Breward
-- Create date: AndyB 17/7/2024 
-- Descripton:	SP to retrieve the Settings associated with the Jobs (used by the ImportService)
-- Modified:    AndyB 07/Sep/2024 Added ImportServiceSendTestEmail
-- Modified:    AndyB 09/Sep/2024 Re-wrote in order to include DefaultValue
-- Modified:    AndyB 01/Dec/2025 Added OAuth prefix for v1.41
--
-- Example:     exec sp_JobSchedule_SystemSettings_Select
-- =============================================

SELECT 
	SystemSettingName              AS SystemSettingName,
	ISNull(DefaultValue, '')       AS SystemSettingDefaultValue, 
	ISNull(SystemSettingValue, '') AS SystemSettingValue
FROM
    SystemSettingValue
WHERE
    SystemSettingName LIKE 'ImportService%'
OR
    SystemSettingName LIKE 'Email%'
OR
    SystemSettingName LIKE 'OAuth%'
ORDER BY
    1
GO










/****** Object:  StoredProcedure [dbo].[sp_WebPlus_Authentication_Validate]    Script Date: 16/12/2025 10:58:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sp_WebPlus_Authentication_Validate]
    @AuthType VARCHAR(50)
AS
-- ===============================================================
-- Author:		Andrew Breward
-- Create date: AndyB 11/12/2025 
-- Descripton:	SP to validate the AuthType passed in against the SystemSettingValue (OneGradePlusAuthenticationType)
-- Example:     exec sp_WebPlus_Authentication_Validate 'WINDOWS'
-- ===============================================================

DECLARE @ret          VARCHAR(500);
DECLARE @SettingValue VARCHAR(50);

	IF NULLIF(LTRIM(RTRIM(@AuthType)), '') IS NULL
	BEGIN
		SELECT 'Parameter @AuthType is required (sp_WebPlus_Authentication_Validate)' AS ReturnValue;
		RETURN;
	END

	SELECT 
		@SettingValue = IsNull(NullIf(SystemSettingValue,''), DefaultValue) 
	FROM 
		SystemSettingValue
	WHERE 
		SystemSettingName = 'OneGradePlusAuthenticationType';

	SET @ret =
		CASE 
			WHEN @SettingValue IS NULL
				THEN 'Missing Authentication Setting (OneGradePlusAuthenticationType)'
			WHEN NULLIF(@SettingValue, '') IS NULL
				THEN 'Missing Authentication setting value (OneGradePlusAuthenticationType)'
			WHEN ',' + @SettingValue + ',' LIKE '%,' + @AuthType + ',%'
				THEN ''  -- match, return empty string
			ELSE 
				'Authentication settings don''t match (OneGradePlusAuthenticationType=' + @SettingValue + ')'
    END;

	SELECT @ret AS ReturnValue;
GO







/****** Object:  StoredProcedure [dbo].[sp_WebPlus_Errorlog_Create]    Script Date: 16/12/2025 08:12:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_WebPlus_Errorlog_Create]
@Username       VARCHAR(255),
@ErrorWhen      DateTime,
@CallingRoutine VARCHAR(500),
@ErrorDetails   VARCHAR(max),
@Callstack      VARCHAR(max) 
AS
-- =============================================
-- Author:		Andrew Breward
-- Create date: AndyB 7/6/2025 v1.40
-- Descripton:	SP to create a new Errorlog recordGroup
-- Modified:    AndyB 16/12/2025 v1.41 Incresed Username to VARCHAR(255)
-- =============================================

DECLARE @newID INT;

	--Delete records older than 7 days
	DELETE FROM 
		OGP_Errorlog 
	WHERE 
		ErrorWhen < GetDate()-7;


	--Insert the new record
	INSERT INTO 
		OGP_Errorlog 
			(ErrorWhen, Username, CallingRoutine, ErrorDetails, Callstack) 
	VALUES 
		(@ErrorWhen, @Username, @CallingRoutine, @ErrorDetails, @Callstack);

	--get the new ID
	SELECT @newID = SCOPE_IDENTITY();

SELECT @newID AS ReturnValue;
GO





/****** Object:  StoredProcedure [dbo].[sp_WebPlus_Markbook_EnrolmentElementResubUpdateCopy]    Script Date: 16/12/2025 09:45:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER   PROCEDURE [dbo].[sp_WebPlus_Markbook_EnrolmentElementResubUpdateCopy]
    @EnrolmentElementId INT,

	--audit
	@UpdatedBy NVARCHAR(255),
    @UpdatedByUserID INT,

    @Complete BIT,
    @Grade VARCHAR(12),
    @Points DECIMAL(19, 2),    
    @Comment VARCHAR(512),
    @UserDefinedStringShort1 NVARCHAR(50),
    @UserDefinedStringShort2 NVARCHAR(50),
    @UserDefinedStringLong1 NVARCHAR(MAX),
    @UserDefinedStringLong2 NVARCHAR(MAX),
    @UserDefinedCheck1 BIT,
    @UserDefinedCheck2 BIT,
    @UserDefinedNumeric1 DECIMAL(19, 2),
    @UserDefinedNumeric2 DECIMAL(19, 2),

	--Dates
    @DateExpected DATETIME,
    @DateSubmitted DATETIME,
	@DateSet DATETIME,
	@UserDefinedDate1 DATETIME,
    @UserDefinedDate2 DATETIME,
	@DateMarked DATETIME,
    @FeedbackStudent NVARCHAR(MAX),
    @FeedbackStudentDateUpdated DATETIME
AS
-- =========================================================================
-- Modified:    AndyB 16/12/2025 v1.41 Increased @UpdatedBy length to 255
-- =========================================================================
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

        -- Insert the current row from OGP_EnrolmentElement into OGP_EnrolmentElementResubmission
        INSERT INTO [OGP_EnrolmentElementResubmission] (
            [OGP_EnrolmentElementID], [Complete], [Grade], [Points], [DateMarked], [Comment], 
            [UpdatedBy], [UpdatedByUserID], [UpdatedWhen], [DateSubmitted], 
            [UserDefinedDate1], [UserDefinedDate2],
            [UserDefinedStringShort1], [UserDefinedStringShort2], [UserDefinedStringLong1], 
            [UserDefinedStringLong2],
            [UserDefinedCheck1], [UserDefinedCheck2], [UserDefinedNumeric1], 
            [UserDefinedNumeric2],
            DateExpected,DateSet,
            FeedbackStudent,
            FeedbackStudentDateUpdated
        )
        SELECT 
            [ID], [Complete], [Grade], [Points], [DateMarked], [Comment],
            [UpdatedBy], [UpdatedByUserID], [UpdatedWhen], [DateSubmitted],
            [UserDefinedDate1], [UserDefinedDate2],
            [UserDefinedStringShort1], [UserDefinedStringShort2], [UserDefinedStringLong1], [UserDefinedStringLong2],
            [UserDefinedCheck1], [UserDefinedCheck2], [UserDefinedNumeric1], [UserDefinedNumeric2],DateExpected,DateSet,
            FeedbackStudent,
            FeedbackStudentDateUpdated
        FROM [OGP_EnrolmentElement]
        WHERE [ID] = @EnrolmentElementId;

        -- Update the row in the OGP_EnrolmentElement table
        UPDATE [OGP_EnrolmentElement]
        SET [Complete] = @Complete,
            [Grade] = @Grade,
            [Points] = @Points,
            [DateMarked] = @DateMarked,
            [Comment] = @Comment,
            [UpdatedBy] = @UpdatedBy,
            [UpdatedByUserID] = @UpdatedByUserID,
            [UpdatedWhen] = dbo.UKLocalTimeFromUtc(GETUTCDATE()),
            [UserDefinedDate1] = @UserDefinedDate1,
            [UserDefinedDate2] = @UserDefinedDate2,
            [UserDefinedStringShort1] = @UserDefinedStringShort1,
            [UserDefinedStringShort2] = @UserDefinedStringShort2,
            [UserDefinedStringLong1] = @UserDefinedStringLong1,
            [UserDefinedStringLong2] = @UserDefinedStringLong2,
            [UserDefinedCheck1] = @UserDefinedCheck1,
            [UserDefinedCheck2] = @UserDefinedCheck2,
            [UserDefinedNumeric1] = @UserDefinedNumeric1,
            [UserDefinedNumeric2] = @UserDefinedNumeric2,
			DateSubmitted = @DateSubmitted,
			DateExpected = @DateExpected,
			DateSet = @DateSet,
            FeedbackStudent = @FeedbackStudent,
            FeedbackStudentDateUpdated = @FeedbackStudentDateUpdated
        WHERE [ID] = @EnrolmentElementId;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END;

        -- Raise the error to the caller
        DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;
        SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH
END;
GO




/****** Object:  StoredProcedure [dbo].[sp_WebPlus_MarkBook_SaveMarkbookGrade]    Script Date: 16/12/2025 09:49:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_WebPlus_MarkBook_SaveMarkbookGrade]
    @Type NVARCHAR(50),
    @Value NVARCHAR(MAX),
    @UpdatedBy NVARCHAR(255),
    @UpdatedByUserID int,
    @GroupID INT,
    @EnrolmentID INT,
    @ElementID INT,
	@OGP_EnrolmentElementID_Output INT OUTPUT 
AS
-- =========================================================================
-- Modified:    AndyB 16/12/2025 v1.41 Increased @UpdatedBy length to 255
-- =========================================================================
BEGIN
    DECLARE @OGP_GroupElementID INT;
    DECLARE @DateValue DATETIME;
    DECLARE @CompleteValue INT;
    DECLARE @userdefinedcheck1 INT;
    DECLARE @userdefinedcheck2 INT;
    DECLARE @OGP_EnrolmentElementID INT;
	DECLARE @Now DATETIME = dbo.UKLocalTimeFromUtc(GETUTCDATE());
	
    -- Get Group Element ID
    SELECT @OGP_GroupElementID = ID
    FROM OGP_GroupElement
    WHERE OGP_ElementID = @ElementID AND OGP_GroupID = @GroupID;

    -- Check if Element ID and Enrolment ID exist
    IF @ElementID IS NULL OR @EnrolmentID IS NULL OR @OGP_GroupElementID IS NULL
    BEGIN
        -- Error handling
        RETURN;
    END

    IF @Type = 'complete'
    BEGIN
        SET @CompleteValue = CASE WHEN @Value IN ('True', 'true', '1') THEN 1 ELSE 0 END;
    END;

	  IF @Type = 'userdefinedcheck1'
    BEGIN
        SET @userdefinedcheck1 = CASE WHEN @Value IN ('True', 'true', '1') THEN 1 ELSE 0 END;
    END;

	  IF @Type = 'userdefinedcheck2'
    BEGIN
        SET @userdefinedcheck2 = CASE WHEN @Value IN ('True', 'true', '1') THEN 1 ELSE 0 END;
    END;

    -- Check if the row exists in OGP_EnrolmentElement
    IF NOT EXISTS (SELECT 1 
                   FROM OGP_EnrolmentElement 
                   WHERE OGP_ElementID = @ElementID AND OGP_EnrolmentID = @EnrolmentID)
    BEGIN
        -- Row does not exist, perform insert with minimal data
        INSERT INTO OGP_EnrolmentElement (OGP_ElementID, OGP_EnrolmentID, UpdatedBy, UpdatedByUserID, UpdatedWhen)
        VALUES (@ElementID, @EnrolmentID, @UpdatedBy, @UpdatedByUserID , @Now);

        -- Get the newly inserted OGP_EnrolmentElement ID
        SELECT @OGP_EnrolmentElementID = SCOPE_IDENTITY();

    END

    BEGIN
        -- Row always exists as create before, get the OGP_EnrolmentElement ID
        SELECT @OGP_EnrolmentElementID = ID 
        FROM OGP_EnrolmentElement 
        WHERE OGP_ElementID = @ElementID AND OGP_EnrolmentID = @EnrolmentID;

        -- Perform update
        IF @Type = 'complete'
        BEGIN
            UPDATE OGP_EnrolmentElement
            SET Complete = @CompleteValue, UpdatedBy = @UpdatedBy, UpdatedByUserID = @UpdatedByUserID, UpdatedWhen = @Now
            WHERE OGP_ElementID = @ElementID AND OGP_EnrolmentID = @EnrolmentID;
        END
        ELSE IF @Type = 'grade'
        BEGIN
            UPDATE OGP_EnrolmentElement
            SET Grade = @Value, UpdatedBy = @UpdatedBy,UpdatedByUserID = @UpdatedByUserID, UpdatedWhen = @Now
            WHERE OGP_ElementID = @ElementID AND OGP_EnrolmentID = @EnrolmentID;
        END
        ELSE IF @Type = 'points'
        BEGIN
			IF @Value = ''
				SET @Value = NULL;

            UPDATE OGP_EnrolmentElement
            SET Points = @Value, UpdatedBy = @UpdatedBy,UpdatedByUserID = @UpdatedByUserID, UpdatedWhen = @Now
            WHERE OGP_ElementID = @ElementID AND OGP_EnrolmentID = @EnrolmentID;
        END
        ELSE IF @Type = 'comment'
        BEGIN
            UPDATE OGP_EnrolmentElement
            SET Comment = @Value, UpdatedBy = @UpdatedBy,UpdatedByUserID = @UpdatedByUserID, UpdatedWhen = @Now
            WHERE OGP_ElementID = @ElementID AND OGP_EnrolmentID = @EnrolmentID;
        END

		ELSE IF @Type = 'userdefinedstringshort1'--'shortstring1'
        BEGIN
            UPDATE OGP_EnrolmentElement
            SET UserDefinedStringShort1 = @Value, UpdatedBy = @UpdatedBy,UpdatedByUserID = @UpdatedByUserID, UpdatedWhen = @Now
            WHERE OGP_ElementID = @ElementID AND OGP_EnrolmentID = @EnrolmentID;
        END
		ELSE IF @Type = 'userdefinedstringshort2'--'shortstring2'
        BEGIN
            UPDATE OGP_EnrolmentElement
            SET UserDefinedStringShort2 = @Value, UpdatedBy = @UpdatedBy,UpdatedByUserID = @UpdatedByUserID, UpdatedWhen = @Now
            WHERE OGP_ElementID = @ElementID AND OGP_EnrolmentID = @EnrolmentID;
        END
		ELSE IF @Type = 'userdefinedstringlong1'--'longstring1'
        BEGIN
            UPDATE OGP_EnrolmentElement
            SET UserDefinedStringLong1 = @Value, UpdatedBy = @UpdatedBy,UpdatedByUserID = @UpdatedByUserID, UpdatedWhen = @Now
            WHERE OGP_ElementID = @ElementID AND OGP_EnrolmentID = @EnrolmentID;
        END
		ELSE IF @Type = 'userdefinedstringlong2'--'longstring2'
        BEGIN
            UPDATE OGP_EnrolmentElement
            SET UserDefinedStringLong2 = @Value, UpdatedBy = @UpdatedBy,UpdatedByUserID = @UpdatedByUserID, UpdatedWhen = @Now
            WHERE OGP_ElementID = @ElementID AND OGP_EnrolmentID = @EnrolmentID;
        END
		ELSE IF @Type = 'userdefinedcheck1'
        BEGIN
            UPDATE OGP_EnrolmentElement
            SET UserDefinedCheck1 = @userdefinedcheck1, UpdatedBy = @UpdatedBy,UpdatedByUserID = @UpdatedByUserID, UpdatedWhen = @Now
            WHERE OGP_ElementID = @ElementID AND OGP_EnrolmentID = @EnrolmentID;
        END
		ELSE IF @Type = 'userdefinedcheck2'
        BEGIN
            UPDATE OGP_EnrolmentElement
            SET UserDefinedCheck2 = @userdefinedcheck2, UpdatedBy = @UpdatedBy,UpdatedByUserID = @UpdatedByUserID, UpdatedWhen = @Now
            WHERE OGP_ElementID = @ElementID AND OGP_EnrolmentID = @EnrolmentID;
        END
		ELSE IF @Type = 'userdefinednumeric1'
        BEGIN
			IF @Value = ''
				SET @Value = NULL;

            UPDATE OGP_EnrolmentElement
            SET UserDefinedNumeric1 = @Value, UpdatedBy = @UpdatedBy,UpdatedByUserID = @UpdatedByUserID, UpdatedWhen = @Now
            WHERE OGP_ElementID = @ElementID AND OGP_EnrolmentID = @EnrolmentID;
        END
		ELSE IF @Type = 'userdefinednumeric2'
        BEGIN
			IF @Value = ''
				SET @Value = NULL;

            UPDATE OGP_EnrolmentElement
            SET UserDefinedNumeric2 = @Value, UpdatedBy = @UpdatedBy,UpdatedByUserID = @UpdatedByUserID, UpdatedWhen = @Now
            WHERE OGP_ElementID = @ElementID AND OGP_EnrolmentID = @EnrolmentID;
        END
        ELSE IF @Type = 'feedbackstudent'
        BEGIN
            UPDATE OGP_EnrolmentElement
            SET feedbackstudent = @Value, UpdatedBy = @UpdatedBy,UpdatedByUserID = @UpdatedByUserID, UpdatedWhen = @Now
            WHERE OGP_ElementID = @ElementID AND OGP_EnrolmentID = @EnrolmentID;
        END
        
    END

    -- Handle date updates
    IF @Type IN ('datesubmitted', 'dateexpected', 'datemarked', 'dateresub', 'dateresubmarked', 'dateset','userdefineddate1','userdefineddate2',
    'feedbackstudentdateupdated')
    BEGIN
        IF @Value IS NULL OR @Value = ''
        BEGIN
            SET @DateValue = NULL;
        END
        ELSE
        BEGIN
            SET @DateValue = CONVERT(DATETIME, @Value, 3); -- Convert the string to datetime. 3= '13/01/24'
        END

        IF @Type = 'datesubmitted'
        BEGIN
            UPDATE OGP_EnrolmentElement
            SET DateSubmitted = @DateValue, UpdatedBy = @UpdatedBy,UpdatedByUserID = @UpdatedByUserID, UpdatedWhen = @Now
            WHERE OGP_ElementID = @ElementID AND OGP_EnrolmentID = @EnrolmentID;
        END
        ELSE IF @Type = 'dateexpected'
        BEGIN
            UPDATE OGP_EnrolmentElement
            SET DateExpected = @DateValue, UpdatedBy = @UpdatedBy,UpdatedByUserID = @UpdatedByUserID, UpdatedWhen = @Now
            WHERE OGP_ElementID = @ElementID AND OGP_EnrolmentID = @EnrolmentID;
        END
        ELSE IF @Type = 'datemarked'
        BEGIN
            UPDATE OGP_EnrolmentElement
            SET DateMarked = @DateValue, UpdatedBy = @UpdatedBy,UpdatedByUserID = @UpdatedByUserID, UpdatedWhen = @Now
            WHERE OGP_ElementID = @ElementID AND OGP_EnrolmentID = @EnrolmentID;
        END
        ELSE IF @Type IN ('dateresub', 'dateresubmarked')
        BEGIN
            -- Check if the row exists in OGP_EnrolmentElementResubmission
            IF NOT EXISTS (SELECT 1 
                           FROM OGP_EnrolmentElementResubmission eer
                           WHERE eer.OGP_EnrolmentElementID = @OGP_EnrolmentElementID)
            BEGIN
                -- Insert new row into OGP_EnrolmentElementResubmission
                INSERT INTO OGP_EnrolmentElementResubmission (OGP_EnrolmentElementID, DateSubmitted, DateMarked, UpdatedBy,UpdatedByUserID, UpdatedWhen)
                VALUES (@OGP_EnrolmentElementID, 
                        CASE WHEN @Type = 'dateresub' THEN @DateValue ELSE NULL END,
                        CASE WHEN @Type = 'dateresubmarked' THEN @DateValue ELSE NULL END,
                        @UpdatedBy,
                        @UpdatedByUserID,
                        @Now);
            END
            ELSE
            BEGIN
                -- Row exists, perform update
                IF @Type = 'dateresub'
                BEGIN
                    UPDATE eer
                    SET eer.DateSubmitted = @DateValue, eer.UpdatedBy = @UpdatedBy,UpdatedByUserID = @UpdatedByUserID, eer.UpdatedWhen = @Now
                    FROM OGP_EnrolmentElementResubmission eer
                    WHERE eer.OGP_EnrolmentElementID = @OGP_EnrolmentElementID;
                END
                ELSE IF @Type = 'dateresubmarked'
                BEGIN
                    UPDATE eer
                    SET eer.DateMarked = @DateValue, eer.UpdatedBy = @UpdatedBy,UpdatedByUserID = @UpdatedByUserID, eer.UpdatedWhen = @Now
                    FROM OGP_EnrolmentElementResubmission eer
                    WHERE eer.OGP_EnrolmentElementID = @OGP_EnrolmentElementID;
                END
            END
        END
        ELSE IF @Type = 'dateset'
        BEGIN
            UPDATE OGP_EnrolmentElement
            SET DateSet = @DateValue, UpdatedBy = @UpdatedBy,UpdatedByUserID = @UpdatedByUserID, UpdatedWhen = @Now
            WHERE OGP_ElementID = @ElementID AND OGP_EnrolmentID = @EnrolmentID;
        END
		ELSE IF @Type = 'userdefineddate2'
        BEGIN
            UPDATE OGP_EnrolmentElement
            SET UserDefinedDate2 = @DateValue, UpdatedBy = @UpdatedBy,UpdatedByUserID = @UpdatedByUserID, UpdatedWhen = @Now
            WHERE OGP_ElementID = @ElementID AND OGP_EnrolmentID = @EnrolmentID;
        END
		ELSE IF @Type = 'userdefineddate1'
        BEGIN
            UPDATE OGP_EnrolmentElement
            SET UserDefinedDate1 = @DateValue, UpdatedBy = @UpdatedBy,UpdatedByUserID = @UpdatedByUserID, UpdatedWhen = @Now
            WHERE OGP_ElementID = @ElementID AND OGP_EnrolmentID = @EnrolmentID;
        END
        ELSE IF @Type = 'feedbackstudentdateupdated'
        BEGIN
            UPDATE OGP_EnrolmentElement
            SET feedbackstudentdateupdated = @DateValue, UpdatedBy = @UpdatedBy,UpdatedByUserID = @UpdatedByUserID, UpdatedWhen = @Now
            WHERE OGP_ElementID = @ElementID AND OGP_EnrolmentID = @EnrolmentID;
        END
    END

	    SET @OGP_EnrolmentElementID_Output = @OGP_EnrolmentElementID;
END;

GO




/****** Object:  StoredProcedure [dbo].[sp_WebPlus_DataRollover_MarkbookElements_Copy]    Script Date: 16/12/2025 10:00:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[sp_WebPlus_DataRollover_MarkbookElements_Copy]
  @CopyFrom_AcademicYearID AS VARCHAR(5),  -- Source Year:
  @CopyTo_AcademicYearID AS VARCHAR(5)     -- Target Year: 

AS

-- ===============================================================
-- CREATED AMG 26/02/2025
-- This will copy markbook Elements from one year and roll them forward into another year.
-- It will copy elements from one course into a course in the future year with the same CourseCode
-- If ANY Elements exist in the future Course already, then NO Elements will be copied into that Course.
-- The Newly created Elements are then assigned to any Groups that exist for the Course - As long as those groups DO NOT have any Elements allocated to them.
--
-- Modified:    AndyB 16/12/2025 v1.41 Increased UpdatedBy length to 255
-- ===============================================================

--DECLARE @CopyFrom_AcademicYearID AS VARCHAR(5)
--SELECT @CopyFrom_AcademicYearID = '24/25'

--DECLARE @CopyTo_AcademicYearID AS VARCHAR(5)
--SELECT @CopyTo_AcademicYearID = '25/26'

DECLARE @MaxElementID AS INT
SELECT @MaxElementID = (SELECT MAX(ID) FROM OGP_Element) + 100 --Push it on 100 records (in the unlikely event someone creates recs in OGP_Element between this and the INSERT later)

DECLARE @SQL AS VARCHAR(MAX)

SELECT @SQL = '

DECLARE @ReturnText AS VARCHAR(1000)
SELECT @ReturnText = ''''

CREATE TABLE #Temp_OGP_Element 
(
	ID int IDENTITY (' + CAST(@MaxElementID AS VARCHAR) + ',1) NOT NULL,
	OGP_ElementTypeID int NOT NULL,
	Old_CourseID int NOT NULL,
	ElementCode varchar(20) NOT NULL,
	[Description] varchar(512) NOT NULL,
	Size decimal(19, 2) NOT NULL,
	OutOfPoints decimal(19, 2) NULL,
	UpdatedBy varchar(255) NOT NULL,
	UpdatedWhen datetime NOT NULL,
	RecordStatus varchar(30) NOT NULL,
	Old_TopLevelParentID int NULL,
	Old_DirectParentID int NULL,
	OrderBy int NOT NULL,
	OGP_ElementLevelID int NULL,
	CourseCode varchar(50) NOT NULL,
	AcademicYearID varchar(5) NOT NULL,
	Old_OGP_ElementID int NULL,
	TopLevelParentID int NULL,
	DirectParentID int NULL,
	CourseID int NULL
)

--Get the elements...
INSERT #Temp_OGP_Element (OGP_ElementTypeID, Old_CourseID, ElementCode, [Description], Size, OutOfPoints, UpdatedBy, UpdatedWhen, RecordStatus, Old_TopLevelParentID, Old_DirectParentID, OrderBy, OGP_ElementLevelID,
	Course.CourseCode, Course.AcademicYearID, Old_OGP_ElementID)
SELECT 
	OGP_ElementTypeID, CourseID AS Old_CourseID, ElementCode, [Description], Size, OutOfPoints, UpdatedBy, UpdatedWhen, RecordStatus, TopLevelParentID, DirectParentID, OrderBy, OGP_ElementLevelID,
	Course.CourseCode, Course.AcademicYearID, OGP_Element.ID AS Old_OGP_ElementID
FROM
	OGP_Element
	INNER JOIN Course ON OGP_Element.CourseID = Course.ID
WHERE
	Course.AcademicYearID = ''' + @CopyFrom_AcademicYearID + '''
	AND EXISTS (SELECT OGP_GroupElement.OGP_ElementID FROM OGP_GroupElement WHERE OGP_GroupElement.OGP_ElementID = OGP_Element.ID) --We only want elements that are allocated to Groups
	AND OGP_Element.RecordStatus <> ''Obsolete'' --we dont want any obsolete elements

--UPDATE the TopLevelParentID and DirectParentID...
UPDATE #Temp_OGP_Element 
SET #Temp_OGP_Element.TopLevelParentID = t.ID
FROM #Temp_OGP_Element INNER JOIN #Temp_OGP_Element t ON #Temp_OGP_Element.Old_TopLevelParentID = t.Old_OGP_ElementID

UPDATE #Temp_OGP_Element 
SET #Temp_OGP_Element.DirectParentID = t.ID
FROM #Temp_OGP_Element INNER JOIN #Temp_OGP_Element t ON #Temp_OGP_Element.Old_DirectParentID = t.Old_OGP_ElementID

--Now update the CourseIDs - make a match on the CourseCodes from last year to this year...
UPDATE #Temp_OGP_Element
SET #Temp_OGP_Element.CourseID = Course.ID
FROM #Temp_OGP_Element INNER JOIN Course ON #Temp_OGP_Element.CourseCode = Course.CourseCode
WHERE Course.AcademicYearID = ''' + @CopyTo_AcademicYearID + '''

--Elements must have CourseIDs, so remove any without CourseIDs
DELETE FROM #Temp_OGP_Element WHERE CourseID IS NULL

--Remove any recs from #Temp_OGP_Element for those Courses in the New AcYr that have any records - any course that already has elements wont get any more rolled on...
DELETE FROM #Temp_OGP_Element 
WHERE #Temp_OGP_Element.CourseID IN (SELECT OGP_Element.CourseID FROM OGP_Element INNER JOIN Course ON Course.ID = OGP_Element.CourseID WHERE Course.AcademicYearID = ''' + @CopyTo_AcademicYearID + ''' )

--Find out the Courses we have and the Groups we need to allocate Elements to...
SELECT DISTINCT CourseID INTO #TempCourse FROM #Temp_OGP_Element
SELECT OGP_Group.ID AS OGP_GroupID, OGP_Group.CourseID INTO #TempGroup FROM OGP_Group INNER JOIN #TempCourse ON OGP_Group.CourseID = #TempCourse.CourseID

IF (SELECT MAX(ID) FROM OGP_Element) < ' + CAST(@MaxElementID AS VARCHAR) + ' --Only do these INSERTs if the max ElementID is less than the @MaxElementID variable we loaded earlier.
BEGIN

	SET IDENTITY_INSERT OGP_Element ON
	--INSERT Elements where they dont already exist and only for those Courses with no Elements.
	INSERT OGP_Element (ID, OGP_ElementTypeID, CourseID, ElementCode, Description, Size, OutOfPoints, UpdatedBy, UpdatedWhen, RecordStatus, TopLevelParentID, DirectParentID, OrderBy, OGP_ElementLevelID)
	SELECT ID, OGP_ElementTypeID, CourseID, ElementCode, Description, Size, OutOfPoints, UpdatedBy, UpdatedWhen, RecordStatus, TopLevelParentID, DirectParentID, OrderBy, OGP_ElementLevelID
	FROM #Temp_OGP_Element
	WHERE NOT EXISTS (SELECT OGP_Element.ID FROM OGP_Element 
						WHERE OGP_Element.OGP_ElementTypeID = #Temp_OGP_Element.OGP_ElementTypeID AND OGP_Element.CourseID = #Temp_OGP_Element.CourseID
						AND OGP_Element.ElementCode = #Temp_OGP_Element.ElementCode AND ISNULL(OGP_Element.TopLevelParentID,0) = ISNULL(#Temp_OGP_Element.TopLevelParentID,0)
					)

	SET IDENTITY_INSERT OGP_Element OFF

	--Next, assign the elements to Groups. (Look at the CourseIDs and any groups that have those CourseIDs in the @NewAcYrID get elements allocated)
	--Only assign elements to groups that have no elements and use a WHERE NOT EXISTS for belt and braces.
	INSERT OGP_GroupElement (OGP_GroupID, OGP_ElementID)
	SELECT #TempGroup.OGP_GroupID, #Temp_OGP_Element.ID 
	FROM #Temp_OGP_Element INNER JOIN #TempGroup ON #Temp_OGP_Element.CourseID = #TempGroup.CourseID
	WHERE #TempGroup.OGP_GroupID IN (
									SELECT #TempGroup.OGP_GroupID FROM OGP_GroupElement 
									RIGHT JOIN #TempGroup ON OGP_GroupElement.OGP_GroupID = #TempGroup.OGP_GroupID
									GROUP BY #TempGroup.OGP_GroupID HAVING COUNT(OGP_GroupElement.ID) = 0
										)
	AND NOT EXISTS (SELECT OGP_GroupElement.ID FROM OGP_GroupElement WHERE OGP_GroupElement.OGP_GroupID = #TempGroup.OGP_GroupID AND OGP_GroupElement.OGP_ElementID = #Temp_OGP_Element.ID)
	
	DECLARE @CountElements AS INTEGER
	SELECT @CountElements = (SELECT Count (OGP_Element.ID) FROM OGP_Element WHERE OGP_Element.ID >  ' + CAST(@MaxElementID AS VARCHAR) + ')
	SELECT @ReturnText = (SELECT CAST(@CountElements AS VARCHAR) + '' Elements Created and Allocated to Groups'')

END
ELSE
BEGIN
	SELECT @ReturnText = ''No Elements and Groups Created because there is too much activity in OneGrade''
END

SELECT @ReturnText
'

EXEC(@SQL)

GO




/****** Object:  StoredProcedure [dbo].[sp_WebPlus_StudentUser_Select]    Script Date: 16/12/2025 10:08:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sp_WebPlus_StudentUser_Select]
	@AcademicYearID  VARCHAR(5),
	@UserID   INT,
    @Username VARCHAR(255)
AS
-- ===============================================================
-- Author:		Andrew Breward
-- Create date: AndyB 15/01/2023 v1.0.0
-- Descripton:	SP to retrieve the Student details 
-- Example:     exec sp_WebPlus_StudentUser_Select '22/23', 'AndyB', 1840
-- Modified:    AndyB 16/12/2025 v1.41 Increased @Username length to 255
-- ===============================================================

	SELECT 
		*
	FROM
		vStudentUserInformation 
	WHERE 
		UserName       = @Username 		  AND 
		UserID         = @UserID          AND
		AcademicYearID = @AcademicYearID


GO




/****** Object:  StoredProcedure [dbo].[sp_WebPlus_AcademicYearsForStudent_Select]    Script Date: 16/12/2025 10:12:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sp_WebPlus_AcademicYearsForStudent_Select]
	@UserID   INT,
    @Username VARCHAR(255)
AS
-- ===============================================================
-- Author:		Andrew Breward
-- Create date: AndyB 22/08/2023 v1.0.0
-- Descripton:	SP to retrieve the allowed AcademicYear values for a particular Student.
--              It also uses GetAcademicYearFromDate() to determine the CurrentAcademicYear 
-- Example:     exec sp_WebPlus_AcademicYearsForStudent_Select
-- Modified:    AndyB 16/12/2025 v1.41 Increased @Username length to 255
-- ===============================================================

SELECT DISTINCT 
	AcademicYearID,
	CASE WHEN AcademicYearID = dbo.GetAcademicYearFromDate(GetDate()) THEN 1 ELSE 0 END AS IsCurrentAcademicYear 
FROM 
	vStudentUserInformation 
WHERE 
	UserName = @Username 
AND 
	UserID = @UserID 
ORDER BY 
	1

GO


